package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map.Entry;
import java.util.Observable;

public class Database extends Observable {
	private static final Database INSTANCE = new Database();
	private Connection conn;
	private PreparedStatement stmt;

	private Database() {
		conn = null;
	}

	public static Database getInstance() {
		return INSTANCE;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public LinkedList<String> getCustomers(String partialCustomerName) {
		LinkedList<String> list = new LinkedList<String>();
		String sql = "SELECT customerName " + "FROM Customers "
				+ "WHERE customerName LIKE ?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, partialCustomerName + "%");
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				list.add(set.getString("customerName"));
			}
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}	
		return list;

	}

	public void registerNewPallet(String barcode, String product) {		
		String isBlocked = "SELECT DISTINCT blocked from Pallets WHERE productName = ? AND " +
				"barcode NOT IN (SELECT barcode FROM CustomerDeliveries)";	
		String registerNewPallet = "INSERT INTO Pallets(barcode, productDate, productTime, blocked, productName) "
				+ "VALUES(?,CURDATE(),NOW(),?,?)";
		String getIngredients = "Select IngredientName, amount "
			+ "FROM Recipes " + "WHERE productName=?";
		
		try {
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(isBlocked);
			stmt.setString(1, product);			
			ResultSet set = stmt.executeQuery();
			boolean blocked = false;
			if(set.next()){
				blocked  = set.getBoolean("blocked");
			}		
			stmt.close();
			
			stmt = conn.prepareStatement(registerNewPallet);
			stmt.setString(1, barcode);
			stmt.setBoolean(2, blocked);
			stmt.setString(3, product);
			stmt.executeUpdate();
			
			HashMap<String, Double> ingredientAmountMap = new HashMap<String, Double>();			
			stmt = conn.prepareStatement(getIngredients);
			stmt.setString(1, product);
			set = stmt.executeQuery();
			while (set.next()) {
				String name = set.getString("IngredientName");
				double amount = set.getDouble("amount");
				ingredientAmountMap.put(name, amount);
			}			
			Iterator<Entry<String, Double>> itr = ingredientAmountMap.entrySet().iterator();
			while (itr.hasNext()) {
				Entry<String, Double> entry = itr.next();
				updateIngredientAmount(entry.getKey(), entry.getValue());
			}			
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
				setChanged();
				notifyObservers(e.getMessage());
				return;
			} catch (SQLException e1) {
				setChanged();
				notifyObservers(e.getMessage() + " AND " + e1.getMessage());
				return;
			}					
		} finally {
			try {
				conn.setAutoCommit(true);
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
				return;
			}
		}
		setChanged();
		notifyObservers("Succesfully added pallet " +  barcode + " into database");
	}

	private void updateIngredientAmount(String Ingredient, double amount) throws SQLException {
		String sql = "UPDATE Ingredients " + "SET instock = instock-?"
				+ "WHERE ingredientName = ?";		
			stmt = conn.prepareStatement(sql);
			stmt.setDouble(1, amount);
			stmt.setString(2, Ingredient);
			stmt.executeUpdate();

	}

	public LinkedList<String> getBarcodes(String partialBarcode) {
		LinkedList<String> list = new LinkedList<String>();
		String sql = "SELECT barcode " + "FROM Pallets "
				+ "WHERE barcode LIKE ?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, partialBarcode + "%");
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				list.add(set.getString("barcode"));
			}
		} catch (SQLException e) {
			this.setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				this.setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return list;
	}

	public void blockPallet(String barcode, boolean block, int daysBefore) {
		String sql = "UPDATE Pallets SET blocked = ? "
				+ "WHERE productName = (SELECT productName FROM (SELECT productName FROM Pallets WHERE barcode = ? ) AS x) " 
				+ "AND barcode NOT IN (SELECT barcode FROM CustomerDeliveries) " 
				+ "AND productDate >= DATE_SUB((SELECT productDate FROM (SELECT productDate FROM Pallets WHERE barcode = ? ) AS x)" 
				+  ",INTERVAL ? DAY)";
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setBoolean(1, block);
			stmt.setString(2, barcode);
			stmt.setString(3, barcode);
			stmt.setInt(4, daysBefore);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			setChanged();
			notifyObservers(e.getMessage());
			return;
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				
				setChanged();
				notifyObservers(e.getMessage());
				return;
			}
		}
		setChanged();
		String msg ="";
		if(block){
			msg = "blocked";
		}else{
			msg = "unblocked";
		}
		notifyObservers("Sucessfully " + msg + " pallet, also " + msg + " all other pallets of same product");
	}

	public LinkedList<String> getProducts(String partialCustomerName) {
		LinkedList<String> list = new LinkedList<String>();
		String sql = "SELECT productName " + "FROM Products "
				+ "WHERE productName LIKE ?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, partialCustomerName + "%");
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				list.add(set.getString("productName"));
			}
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return list;
	}

	
	public String getPalletInfo(String barcode) {
		String info = "";
		String createView = "create view DeliveryInfo (barcode, orderId, customerName, deliveryDate) as "
				+ "(SELECT barcode, CustomerDeliveries.orderId, customerName, deliveryDate FROM CustomerDeliveries, Orders "
				+ "WHERE CustomerDeliveries.orderId=Orders.orderId)";
		String palletInfo = "SELECT * " + "FROM Pallets " + "LEFT JOIN DeliveryInfo "
			+ "ON Pallets.barcode=DeliveryInfo.barcode "
			+ "WHERE Pallets.barcode = ?";		
		String dropView = "drop view DeliveryInfo;";
		try {
			stmt = conn.prepareStatement(createView);
			stmt.execute();
			
			stmt = conn.prepareStatement(palletInfo);
			stmt.setString(1, barcode);
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				info = set.getString("barcode") + "   "
						+ set.getDate("productDate") + "   "
						+ set.getTime("productTime") + "   "
						+ set.getBoolean("blocked") + "   "
						+ set.getString("productName") + "   "
						+ set.getString("orderId") + "   "
						+ set.getString("deliveryDate") + "   "
						+ set.getString("customerName");
			}
			
			stmt = conn.prepareStatement(dropView);
			stmt.execute();			
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return info;
	}

	public LinkedList<String> getPalletInfoByProduct(String product) {
		LinkedList<String> infoList = new LinkedList<String>();
		String createView = "create view DeliveryInfo (barcode, orderId, customerName, deliveryDate) as "
				+ "(SELECT barcode, CustomerDeliveries.orderId, customerName, deliveryDate FROM CustomerDeliveries, Orders "
				+ "WHERE CustomerDeliveries.orderId=Orders.orderId)";
		
		String selectProducts = "SELECT * " + "FROM Pallets " + "LEFT JOIN DeliveryInfo "
								+ "ON Pallets.barcode=DeliveryInfo.barcode "
								+ "WHERE Pallets.productName = ? ";
		String dropView = "drop view DeliveryInfo;";
		
		try {
			stmt = conn.prepareStatement(createView);
			stmt.execute();
			
			stmt = conn.prepareStatement(selectProducts);
			stmt.setString(1, product);
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				String info = set.getString("barcode") + "   "
						+ set.getDate("productDate") + "   "
						+ set.getTime("productTime") + "   "
						+ set.getBoolean("blocked") + "   "
						+ set.getString("productName") + "   "
						+ set.getString("orderId") + "   "
						+ set.getString("deliveryDate") + "   "
						+ set.getString("customerName");
				infoList.add(info);
			}
			
			stmt = conn.prepareStatement(dropView);
			stmt.execute();
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return infoList;
	}

	public void registerNewDelivery(String barcode, String customer) {
		boolean isBlocked = isBlocked(barcode);
		String registerNewPallet = "INSERT INTO CustomerDeliveries(barcode, orderId, deliveryDate) "
			+ "VALUES(?,last_insert_id(),CURDATE())";
		String createOrder = "INSERT INTO Orders(customerName, orderDate) "
			+ "VALUES(?,CURDATE())";
		if (!isBlocked) {			
			try {
				conn.setAutoCommit(false);
				stmt = conn.prepareStatement(createOrder);
				stmt.setString(1, customer);
				stmt.executeUpdate();
				
				stmt = conn.prepareStatement(registerNewPallet);
				stmt.setString(1, barcode);
				stmt.executeUpdate();
				conn.commit();
				
			} catch (SQLException e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					setChanged();
					notifyObservers(e1.getMessage());
				}
				setChanged();
				notifyObservers(e.getMessage());
			} finally {
				try {
					conn.setAutoCommit(true);
					stmt.close();
				} catch (SQLException e) {
					setChanged();
					notifyObservers(e.getMessage());
				}
			}
		} else {
			this.setChanged();
			notifyObservers("Error: Pallet is blocked");
			return;
		}
		setChanged();
		notifyObservers("Registerd new delivery pallet " + barcode +  " to " + customer);

	}

	private boolean isBlocked(String barcode) {
		String isBlocked = "SELECT blocked from Pallets WHERE barcode = ?";
		boolean blocked = false;
		try {
			stmt = conn.prepareStatement(isBlocked);
			stmt.setString(1, barcode);
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				blocked = set.getBoolean("blocked");
			}
		} catch (SQLException e) {
			this.setChanged();
			notifyObservers(e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return blocked;
	}

	public LinkedList<String> getBlockedProducts() {
		LinkedList<String> infoList = new LinkedList<String>();
		String sql = "SELECT DISTINCT (productName) FROM pallets NATURAL JOIN products WHERE blocked = true";
		try {
			stmt = conn.prepareStatement(sql);

			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				infoList.add(set.getString("productName"));
			}
		} catch (SQLException e) {
			this.setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return infoList;
	}

	public LinkedList<String> getBlockedPalletsByProduct(String s) {
		LinkedList<String> infoList = new LinkedList<String>();
		String sql = "SELECT (barcode) "
				+ "FROM pallets WHERE blocked = true AND productName = ?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, s);
			ResultSet set = stmt.executeQuery();

			while (set.next()) {
				infoList.add(set.getString("barcode"));
			}

		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return infoList;
	}

	public LinkedList<String> getProductDeliveryInfo(String customer) {
		LinkedList<String> infoList = new LinkedList<String>();
		String sql = "SELECT barcode, deliveryDate "
				+ "FROM CustomerDeliveries " + "WHERE orderId "
				+ "IN (SELECT orderId FROM Orders WHERE customerName=?)";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, customer);
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				String info = set.getString("barcode") + "  "
						+ set.getDate("deliveryDate");
				infoList.add(info);
			}
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return infoList;
	}

	public LinkedList<String> PalletDate(String from, String to,
		String fromTime, String toTime) {
		LinkedList<String> infoList = new LinkedList<String>();
		String sql = "SELECT barcode, productDate, productTime "
				+ "FROM Pallets "
				+ "WHERE  "
				+ "productDate BETWEEN ? AND ? AND productTime BETWEEN ? AND ? ORDER BY productDate";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, from);
			stmt.setString(2, to);
			stmt.setString(3, fromTime);
			stmt.setString(4, toTime);
			ResultSet set = stmt.executeQuery();
			while (set.next()) {
				String info = set.getString("barcode") + "  "
						+ set.getString("productDate") + "  "
						+ set.getString("productTime");
				infoList.add(info);
			}
		} catch (SQLException e) {
			setChanged();
			notifyObservers(e.getMessage());
		} finally {
			try {
				stmt.close();
			} catch (SQLException e) {
				setChanged();
				notifyObservers(e.getMessage());
			}
		}
		return infoList;
	}
}
